import datetime
import logging

from sqlTools.mysqlConnect import mysql_db
from tools.configmanager import config

# lianjimysql连接池
mysql_lianji_root = mysql_db(host=config.get("lian_ji.ip"), port=config.get("lian_ji.mysql.port"), database="mysql",
                             user="root", pwd=config.get("lian_ji.mysql.rootPwd"), )
neirongdb = config.get("lian_ji.mysql.database.0")
mysql_neirongdb = mysql_db(host=config.get("lian_ji.ip"), port=config.get("lian_ji.mysql.port"),
                           database=neirongdb.get("dbname"), user=neirongdb.get("user"), pwd=neirongdb.get("pwd"), )
nrljdevdb = config.get("lian_ji.mysql.database.1")
mysql_lianji_dev = mysql_db(host=config.get("lian_ji.ip"), port=config.get("lian_ji.mysql.port"),
                            database=nrljdevdb.get("dbname"), user=nrljdevdb.get("user"), pwd=nrljdevdb.get("pwd"), )
ljrhdb = config.get("lian_ji.mysql.database.2")
mysql_lianji_rh = mysql_db(host=config.get("lian_ji.ip"), port=config.get("lian_ji.mysql.port"),
                           database=ljrhdb.get("dbname"), user=ljrhdb.get("user"), pwd=ljrhdb.get("pwd"), )


def mysql_dev_script(sql_file=None):
    db_instance = mysql_db(host=config.get("lian_ji.ip"), port=config.get("lian_ji.mysql.port"),
                           database=nrljdevdb.get("dbname"), user=nrljdevdb.get("user"), pwd=nrljdevdb.get("pwd"), )

    with open(sql_file, "r") as f:  # 打开文件
        data = f.read()
        lines = data.splitlines()
        sql_data = ""
        # 将--注释开头的全部过滤，将空白行过滤
        for line in lines:
            if len(line) == 0:
                continue
            elif line.startswith("--"):
                continue
            else:
                sql_data += line
        sql_list = sql_data.split(";")[:-1]
        sql_list = [x.replace("\n", " ") if "\n" in x else x for x in sql_list]
        logging.info("------------------- SQL ------------------------")
        logging.info(sql_list)
        with db_instance.cursor() as cursor:
            for sql in sql_list:
                cursor.execute(sql)
            cursor.connection.commit()
        db_instance.close()
    logging.info("--------------------------------------------------")


def mysql_rh_script(db_instance=mysql_lianji_rh, sql_file=None):
    with open(sql_file, "r") as f:  # 打开文件
        data = f.read()
        lines = data.splitlines()
        sql_data = ""
        # 将--注释开头的全部过滤，将空白行过滤
        for line in lines:
            if len(line) == 0:
                continue
            elif line.startswith("--"):
                continue
            else:
                sql_data += line
        sql_list = sql_data.split(";")[:-1]
        sql_list = [x.replace("\n", " ") if "\n" in x else x for x in sql_list]
        logging.info("------------------- SQL ------------------------")
        logging.info(sql_list)
        with db_instance.cursor() as cursor:
            for sql in sql_list:
                cursor.execute(sql)
            cursor.connection.commit()
        db_instance.close()
    logging.info("--------------------------------------------------")


def test():
    with mysql_lianji_dev.cursor() as cursor:
        cursor.execute("show databases;")
        for row in cursor.fetchall():
            print(row)
        cursor.execute("use nrljdevdb;")
    mysql_lianji_root.close()


def mysql_clean_binlog(db_instance=mysql_lianji_root):
    """
    减少binlog大小,需要root权限
    """
    now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    sql = f"purge master logs before '{now}'"
    logging.info(sql)
    cursor = db_instance.execute_sql(sql)
    cursor.connection.commit()
    db_instance.close()


def optimize_table(table_name, db, db_instance):
    """
    执行过大量删除记录或者变长字段缩短后，磁盘空间并不会释放，而是等新数据填充。
    如果没有大量新数据补充，请手动释放磁盘空间。
    """
    logging.warning(f"执行 optimize table {table_name}; 时，可能耗时很久，请耐心等待! ")
    sql = f"OPTIMIZE TABLE {db.get("dbname")}.{table_name}"
    logging.info(sql)
    cursor = db_instance.execute_sql(sql)
    cursor.connection.commit()
    db_instance.close()
